using Dapper;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using MySql.Data.MySqlClient;
using RBAC.Domain;
using RBAC.Interstructrue;
using System.ComponentModel.DataAnnotations.Schema;
using System.Reflection;

namespace RBAC.Interstructrue.Base
{
    public class BaseRepository<T> : IBaseRepository<T> where T : BaseEntity
    {
        private readonly IDbConnectionFactory _connectionFactory;
        private readonly RBACDbContext? _dbContext; // 暂时保留EF Core支持
        private readonly ILogger<BaseRepository<T>> _logger;
        private readonly string _tableName;
        
        // 数据库命令超时时间（秒）
        private const int COMMAND_TIMEOUT_SECONDS = 60;

        public BaseRepository(IDbConnectionFactory connectionFactory, RBACDbContext? dbContext = null, ILogger<BaseRepository<T>> logger = null)
        {
            _connectionFactory = connectionFactory;
            _dbContext = dbContext;
            _logger = logger;
            // 优先使用实体上的 [Table] 特性名称，其次回退到类型名（去除Model后缀）
            var tableAttr = typeof(T).GetCustomAttribute<TableAttribute>();
            _tableName = tableAttr?.Name ?? typeof(T).Name.Replace("Model", "");
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="entity"></param>
        /// <returns>新插入记录的ID</returns>
        public int Create(T entity)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnectionWithRetry();
                var columns = GetColumns(entity);
                var values = GetValues(entity);
                var sql = $"INSERT INTO {_tableName} ({columns}) VALUES ({values}); SELECT LAST_INSERT_ID();";
                
                var command = new Dapper.CommandDefinition(sql, entity, commandTimeout: COMMAND_TIMEOUT_SECONDS);
                var result = connection.QueryFirstOrDefault<int>(command);
                
                _logger?.LogInformation("Successfully created entity in {TableName} with ID {Id}", _tableName, result);
                return result;
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Failed to create entity in {TableName}", _tableName);
                throw;
            }
        }

        /// <summary>
        /// 获取所有数据 (EF Core方式，暂时保留)
        /// </summary>
        /// <returns></returns>
        public IQueryable<T> GetALL()
        {
            if (_dbContext == null)
            {
                throw new InvalidOperationException("EF Core DbContext is not available");
            }
            
            try
            {
                return _dbContext.Set<T>();
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 获取所有数据 (Dapper方式)
        /// </summary>
        /// <returns></returns>
        public List<T> GetAll()
        {
            try
            {
                using var connection = _connectionFactory.CreateConnectionWithRetry();
                var sql = $"SELECT * FROM {_tableName} WHERE IsDeleted = false";
                var command = new Dapper.CommandDefinition(sql, commandTimeout: COMMAND_TIMEOUT_SECONDS);
                return connection.Query<T>(command).ToList();
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Failed to get all entities from {TableName}", _tableName);
                throw;
            }
        }

        /// <summary>
        /// 获取所有数据 (Dapper方式) - 带条件
        /// </summary>
        /// <param name="whereClause">WHERE条件</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public List<T> GetAll(string whereClause = "", object? parameters = null)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnectionWithRetry();
                var sql = $"SELECT * FROM {_tableName} WHERE IsDeleted = false";
                if (!string.IsNullOrEmpty(whereClause))
                {
                    sql += $" AND {whereClause}";
                }
                var command = new Dapper.CommandDefinition(sql, parameters ?? new { }, commandTimeout: COMMAND_TIMEOUT_SECONDS);
                return connection.Query<T>(command).ToList();
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Failed to get entities with condition from {TableName}", _tableName);
                throw;
            }
        }

        /// <summary>
        /// 获取单个实体 (Dapper方式) - 带条件
        /// </summary>
        /// <param name="whereClause">WHERE条件</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public T? GetFirstOrDefault(string whereClause = "", object? parameters = null)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnectionWithRetry();
                var sql = $"SELECT * FROM {_tableName} WHERE IsDeleted = false";
                if (!string.IsNullOrEmpty(whereClause))
                {
                    sql += $" AND {whereClause}";
                }
                sql += " LIMIT 1";
                var command = new Dapper.CommandDefinition(sql, parameters ?? new { }, commandTimeout: COMMAND_TIMEOUT_SECONDS);
                return connection.QueryFirstOrDefault<T>(command);
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Failed to get first entity with condition from {TableName}", _tableName);
                throw;
            }
        }

        /// <summary>
        /// 执行自定义SQL查询 (Dapper方式)
        /// </summary>
        /// <typeparam name="TResult">返回类型</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public List<TResult> Query<TResult>(string sql, object? parameters = null)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnectionWithRetry();
                var command = new Dapper.CommandDefinition(sql, parameters ?? new { }, commandTimeout: COMMAND_TIMEOUT_SECONDS);
                return connection.Query<TResult>(command).ToList();
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Failed to execute custom query: {Sql}", sql);
                throw;
            }
        }

        /// <summary>
        /// 执行自定义SQL查询返回单个结果 (Dapper方式)
        /// </summary>
        /// <typeparam name="TResult">返回类型</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public TResult? QueryFirstOrDefault<TResult>(string sql, object? parameters = null)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnectionWithRetry();
                var command = new Dapper.CommandDefinition(sql, parameters ?? new { }, commandTimeout: COMMAND_TIMEOUT_SECONDS);
                return connection.QueryFirstOrDefault<TResult>(command);
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Failed to execute custom query: {Sql}", sql);
                throw;
            }
        }

        /// <summary>
        /// 分页查询 (Dapper方式)
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页数量</param>
        /// <param name="whereClause">WHERE条件</param>
        /// <param name="orderBy">排序</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public (List<T> Data, int TotalCount) GetPaged(int pageIndex, int pageSize, string whereClause = "", string orderBy = "", object? parameters = null)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnectionWithRetry();
                
                // 构建基础查询
                var baseWhere = "WHERE IsDeleted = false";
                if (!string.IsNullOrEmpty(whereClause))
                {
                    baseWhere += $" AND {whereClause}";
                }

                // 获取总数
                var countSql = $"SELECT COUNT(*) FROM {_tableName} {baseWhere}";
                var countCommand = new Dapper.CommandDefinition(countSql, parameters ?? new { }, commandTimeout: COMMAND_TIMEOUT_SECONDS);
                var totalCount = connection.ExecuteScalar<int>(countCommand);

                // 分页查询
                var offset = (pageIndex - 1) * pageSize;
                var defaultOrderBy = string.IsNullOrEmpty(orderBy) ? "ORDER BY Id DESC" : $"ORDER BY {orderBy}";
                var dataSql = $"SELECT * FROM {_tableName} {baseWhere} {defaultOrderBy} LIMIT {pageSize} OFFSET {offset}";
                var dataCommand = new Dapper.CommandDefinition(dataSql, parameters ?? new { }, commandTimeout: COMMAND_TIMEOUT_SECONDS);
                var data = connection.Query<T>(dataCommand).ToList();

                _logger?.LogInformation("Successfully retrieved {Count} records from {TableName} (Page {PageIndex}, Size {PageSize})", 
                    data.Count, _tableName, pageIndex, pageSize);

                return (data, totalCount);
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                _logger?.LogError(ex, "MySQL error occurred while getting paged data from {TableName}: {Message}", 
                    _tableName, ex.Message);
                throw;
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Unexpected error occurred while getting paged data from {TableName}", _tableName);
                throw;
            }
        }

        /// <summary>
        /// ID获取数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public T GetModel(long id)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnectionWithRetry();
                var sql = $"SELECT * FROM {_tableName} WHERE Id = @Id AND IsDeleted = false";
                var command = new Dapper.CommandDefinition(sql, new { Id = id }, commandTimeout: COMMAND_TIMEOUT_SECONDS);
                return connection.QueryFirstOrDefault<T>(command);
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Failed to get entity with ID {Id} from {TableName}", id, _tableName);
                throw;
            }
        }

        /// <summary>
        /// 修改删除
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int Update(T entity)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnectionWithRetry();
                var setClause = GetUpdateClause(entity);
                var sql = $"UPDATE {_tableName} SET {setClause} WHERE Id = @Id";
                
                var command = new Dapper.CommandDefinition(sql, entity, commandTimeout: COMMAND_TIMEOUT_SECONDS);
                return connection.Execute(command);
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Failed to update entity with ID {Id} in {TableName}", entity.Id, _tableName);
                throw;
            }
        }

        /// <summary>
        /// 获取列名
        /// </summary>
        private string GetColumns(T entity)
        {
            var properties = GetMappedProperties();
            return string.Join(", ", properties.Select(p => p.Name));
        }

        /// <summary>
        /// 获取值占位符
        /// </summary>
        private string GetValues(T entity)
        {
            var properties = GetMappedProperties();
            return string.Join(", ", properties.Select(p => $"@{p.Name}"));
        }

        /// <summary>
        /// 获取更新语句
        /// </summary>
        private string GetUpdateClause(T entity)
        {
            var properties = GetMappedProperties();
            return string.Join(", ", properties.Select(p => $"{p.Name} = @{p.Name}"));
        }

        private static IEnumerable<PropertyInfo> GetMappedProperties()
        {
            return typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public)
                .Where(p => p.Name != "Id" && p.CanWrite)
                .Where(p => !Attribute.IsDefined(p, typeof(NotMappedAttribute)))
                .Where(p => IsScalar(p.PropertyType));
        }

        private static bool IsScalar(Type type)
        {
            // 允许的简单类型：值类型、字符串、日期、十进制、Guid、字节数组、可空上述类型
            if (type.IsPrimitive || type == typeof(string) || type == typeof(DateTime) || type == typeof(decimal) || type == typeof(Guid) || type == typeof(byte[]))
                return true;
            var underlying = Nullable.GetUnderlyingType(type);
            if (underlying != null)
                return IsScalar(underlying);
            return false;
        }

        /// <summary>
        /// 批量插入实体 (Dapper方式)
        /// </summary>
        /// <param name="entities">实体列表</param>
        /// <returns>影响的行数</returns>
        public int CreateBatch(List<T> entities)
        {
            if (entities == null || !entities.Any())
            {
                return 0;
            }

            try
            {
                using var connection = _connectionFactory.CreateConnectionWithRetry();
                
                // 不在这里创建事务，让外部事务管理
                // 这样可以避免事务嵌套问题
                var columns = GetColumns(entities.First());
                var values = GetValues(entities.First());
                var sql = $"INSERT INTO {_tableName} ({columns}) VALUES ({values})";
                
                var command = new Dapper.CommandDefinition(sql, entities, commandTimeout: COMMAND_TIMEOUT_SECONDS);
                var result = connection.Execute(command);
                
                _logger?.LogInformation("Successfully batch created {Count} entities in {TableName}", result, _tableName);
                return result;
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Failed to batch create entities in {TableName}", _tableName);
                throw;
            }
        }

        /// <summary>
        /// 执行自定义SQL (Dapper方式)
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>影响的行数</returns>
        public int Execute(string sql, object? parameters = null)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnectionWithRetry();
                var command = new Dapper.CommandDefinition(sql, parameters ?? new { }, commandTimeout: COMMAND_TIMEOUT_SECONDS);
                return connection.Execute(command);
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Failed to execute SQL: {Sql}", sql);
                throw;
            }
        }

        /// <summary>
        /// 执行存储过程 (Dapper方式)
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="parameters">参数</param>
        /// <returns>影响的行数</returns>
        public int ExecuteStoredProcedure(string procedureName, object? parameters = null)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnectionWithRetry();
                var command = new Dapper.CommandDefinition(procedureName, parameters ?? new { }, commandType: System.Data.CommandType.StoredProcedure, commandTimeout: COMMAND_TIMEOUT_SECONDS);
                return connection.Execute(command);
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Failed to execute stored procedure: {ProcedureName}", procedureName);
                throw;
            }
        }
    }
}
